/**
 * The contents of this file are subject to the license and copyright detailed in the LICENSE and NOTICE files at the root of the source tree and available online at
 * http://www.askosi.org/license.html
 */
package be.destin.skos.core;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;

import org.apache.log4j.Logger;

import be.destin.rdf.changes.Status;
import be.destin.util.RunCommand;
import be.destin.util.Util;

public class SqlSource {

	/** log4j category */
	private static Logger log = Logger.getLogger(SqlSource.class);
	
	//Version query is used to get a string supposed to change when database content changes.
	//Its presence indicates an XML cache must be produced with the loaded ConceptScheme
	protected static final String versionTable = "version";
	
	// Aliasses of the authority concepts codes: code, origin
	protected static final String aliassesTable = "aliasses";
	
	// External "decoration" to authority concepts: code, parent
	protected static final String broadMatchTable = "broadmatches";
	protected static final String narrowMatchTable = "narrowmatches";
	protected static final String relatedMatchTable = "relatedmatches";
	protected static final String relatedTable = "related";
	protected static final String exactMatchTable = "exactmatches";
	protected static final String closeMatchTable = "closematches";
	
	// Names of the authority concepts: code, language, name
	protected static final String namesTable = "labels";

	// Parent(s) of the authority concepts: code, parent
	protected static final String parentsTable = "broaders";
	protected static final String childrenTable = "narrowers";

	// Synonyms of the authority concepts: code, language, synonym
	protected static final String altTable = "alternates";

	// Notes to disambiguate concepts: code, language, note
	protected static final String notesTable = "notes";
	protected static final String usagesTable = "usages";
	
	protected static final String collectionsTable = "collections";
	
	public static String[] splitSQL (String sql) {
		// Split on ";" completely alone!
		/*
		String[] result = sql.split("(?<![;]);(?![;])");
		if (result==null || result.length == 0) return null;
		for (int i = 0; i < result.length; i++) { // ; are escaped by doubling them!
			result[i] = result[i].replace(";;", ";");
		}
		*/
		if (sql == null || sql.isEmpty()) return null;
		// DEL ($7F is used as a place holder for ";"
		String[] result = sql.replace(";;","\177").split(";");
		if (result==null || result.length == 0) return null;
		for (int i=0; i<result.length; i++) {
			result[i] = result[i].replace('\177',';');
		}
		return result;
	}

	protected static PreparedStatement prepareQueryDB(Connection con, String sql) {
		PreparedStatement aStmt = null;
		try {
			aStmt = con.prepareStatement(sql);
		} catch (SQLException ex) {
			log.error(ex.toString()+"="+ex.getSQLState()+", Prepare SQL="+sql);
			Util.printStack(ex);
		}
		return aStmt;
	}

	protected static ResultSet queryDB(String queryForLog, PreparedStatement aStmt, String param) 
	{
		if (aStmt == null) return null;
		if (param == null) {
			log.error("No param for query "+aStmt.toString());
			return null;
		}
		ResultSet rs = null;
		try {
			if (aStmt != null) {
				aStmt.clearParameters();
				if (!param.isEmpty()) {
					aStmt.setString(1, param);
				}
				rs = aStmt.executeQuery();
			}
	/*	
			aStmt = con.createStatement();
			sql = Util.replaceAllNoRegExp(sql,"?","'"+param+"'");
			log.debug("Param="+param+", SQL="+sql);
	*/
		} catch (SQLException ex) {
			log.error(ex.toString()+"="+ex.getSQLState()+", Query SQL="+queryForLog+", param="+param);
		}
		return rs;
	}

	protected static void querySQLmatch(Connection con, ConceptScheme aScheme, String matchSQL, boolean local, 
										String column, Method putMatch ) {
		if (matchSQL != null) {
			String currSchemePrefix = aScheme.getAbout()+SchemeInterface.separator;
			try {
				log.debug(matchSQL);
				Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(matchSQL);

				while (rs.next()) {
					String currCode = getAbout(aScheme, rs, "about", true);
					if (currCode == null) continue;

					String currParent = getAbout(aScheme, rs, column, false);
					if (currParent == null || currParent.isEmpty()) continue;

					Concept entry;
					entry = aScheme.getConcept(currCode);
					if (entry == null && currCode.startsWith(currSchemePrefix)) {
						entry = aScheme.getConcept(currCode.substring(currSchemePrefix.length()+1));
					}
					if (entry == null) {
						String message = aScheme.getAbout() + SchemeInterface.separator + currCode
						+ " does not exist. Referred for "+column+" "
						+ currParent;
						log.error(message);
						aScheme.getErrors().add(message);
					} else {

						if (local) {
							if (currParent.startsWith(currSchemePrefix)) {
								currParent = currParent.substring(currSchemePrefix.length());
								if (currParent.isEmpty()) continue;
							}
						} else {
							if (currParent.indexOf(SchemeInterface.separator) < 0) {
								String message = currCode + ": "+column+" \""+currParent+"\"is not prefixed with scheme code?";
								log.error(message);
								entry.addError(message);
								continue;
							}
						}

						Status currStatus = null;
						try{
							String aStatus = rs.getString("status");
							if (aStatus != null && !aStatus.isEmpty()) {
								currStatus = Status.valueOf(aStatus);
							}
						} catch (java.sql.SQLException ex) {
						}
						String relQualifier = null;
						try {
							relQualifier = rs.getString("qual");
						} catch (java.sql.SQLException ex) {
						}

						try {
							putMatch.invoke(entry, currParent, currStatus, relQualifier);
						} catch (IllegalArgumentException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}

					}
				}
				// close statement
				try {
					stmt.close();
				} catch (SQLException ex) {
					log.error(ex.toString()+", closing statement, SQL="+matchSQL);
					Util.printStack(ex);
				} 
			} catch (SQLException ex) {
				String message = aScheme.getAbout() + ", SQL=" + matchSQL + " : " + ex.toString();
				log.error(message);
				aScheme.getErrors().add(message);
				Util.printStack(ex);
			}
		}
	}

	protected static void load(Properties properties, ConceptScheme aScheme, String configFile, Map<String, String> notationsDefinitions, boolean reload) {
		String dbPool = properties.getProperty("pool");

		Connection con = SqlPool.getSqlConnection(configFile,dbPool);
		if (con == null) return;

		SqlPool poolContext = SqlPool.getSqlPool(dbPool);

		String dbVersion = null;
		ConceptScheme xScheme = null;
		if (!reload) {
			String versionSQL = poolContext.substitute(aScheme,properties.getProperty(versionTable)); // SQL to get the version of the database (indicates it is CACHED)
			if (versionSQL != null && !versionSQL.isEmpty()) {
				try {
					log.debug(versionSQL);
					// create and execute a SELECT
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(versionSQL);
					while (rs.next()) {
						dbVersion = rs.getString("version");
						if (dbVersion != null && !dbVersion.isEmpty()) break;
						dbVersion = null;
					}
					// close statement
					stmt.close();
				} catch (SQLException ex) {
					String message = aScheme.getAbout() + ", SQL=" + versionSQL + " : " + ex.toString()+"="+ex.getSQLState();
					log.error(message);
					aScheme.getErrors().add(message);
					dbVersion = null;
					Util.printStack(ex);
				}
			}
			String cacheFileName = properties.getProperty("cache");
			if (cacheFileName == null || cacheFileName.length() == 0) {
				cacheFileName = configFile.replaceFirst("\\.[a-zA-Z0-9]*$", "")+"-cache.xml";
			}
			if (dbVersion != null) {
				cacheFileName = cacheFileName.replaceFirst("\\.[a-zA-Z0-9]*$", "")+"-"+SkosUtil.normalizeAbout(dbVersion)+".xml";
				InputStream dataInputStream = null;
				String dataInputLocation = "";
				try {
					File cacheFile = new File(cacheFileName);
					dataInputLocation = cacheFile.getAbsolutePath();
					dataInputStream = new FileInputStream(cacheFile);
				} catch( IOException ioe ) {
					dataInputStream = null;
				}
				if (dataInputStream != null) {
					JAXBContext jc = null;
					Unmarshaller u = null;
					try {
						jc = XmlSource.getJC();
						u = jc.createUnmarshaller();
					} catch( JAXBException je ) {
						je.printStackTrace();
						jc = null;
					}
					if (jc != null) {
						try {
							// Loading "title","description","namespace","uri","help","icon","display","create","internalNote","notation","concept"
							xScheme = (ConceptScheme)u.unmarshal( dataInputStream );
						} catch( JAXBException je ) {
							log.error("XML Parsing exception: " + je.toString());
							xScheme = null;
							je.printStackTrace();
						}
						RunCommand.closeAble(dataInputStream, "data input stream " +dataInputLocation);
						if (xScheme != null) {
							XmlSource.loadNewScheme(aScheme,xScheme,reload);
						}
					}
				}
			}
		}

		if (xScheme == null) {
			// languages are deducted from the list of the languages of the list names
			// about, name-fr, name-en...,
			String namesSQL = poolContext.substitute(aScheme,properties.getProperty(namesTable)); // SQL to get the names of the authority concepts

			// about, origin
			String aliassesSQL = poolContext.substitute(aScheme,properties.getProperty(aliassesTable)); // SQL to
			// get the
			// aliasses of the
			// authority concepts
			// codes

			// about, language, synonym
			String synonymsSQL = poolContext.substitute(aScheme,properties.getProperty(altTable)); // SQL to
			// get the
			// synonyms of the
			// authority concepts

			// languages are deducted from the list of the languages of the list names
			// about, note-fr, note-en...,
			String notesSQL = poolContext.substitute(aScheme,properties.getProperty(notesTable)); // SQL to get the notes of the authority concepts

			// about, broader
			String parentsSQL = poolContext.substitute(aScheme,properties.getProperty(parentsTable)); // SQL to get
			String relatedSQL = poolContext.substitute(aScheme,properties.getProperty(relatedTable)); // SQL to get
			String broadMatchSQL = poolContext.substitute(aScheme,properties.getProperty(broadMatchTable)); // SQL to get
			String relatedMatchSQL = poolContext.substitute(aScheme,properties.getProperty(relatedMatchTable)); // SQL to get
			String exactMatchSQL = poolContext.substitute(aScheme,properties.getProperty(exactMatchTable)); // SQL to get
			String closeMatchSQL = poolContext.substitute(aScheme,properties.getProperty(closeMatchTable)); // SQL to get
			String collectionsSQL = poolContext.substitute(aScheme,properties.getProperty(collectionsTable)); // SQL to get

			if (namesSQL != null) {
				try {
					log.debug(namesSQL);
					// create and execute a SELECT
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(namesSQL);

					while (rs.next()) {
						String currLang = TermList.anyLanguage;
						String currCode = getAbout(aScheme, rs, "about", true);
						if (currCode == null) continue;
						Concept entry = aScheme.findAbout_Concept(currCode);
						if (entry== null) {
							entry = Concept.ensure(aScheme,currCode);
							entry = aScheme.putConcept(currCode, entry);
						}
						// get current row values
						try {
							String aLang = rs.getString("lang");
							if (aLang != null && aLang.length() > 0) {
								currLang = aLang;
							}
						} catch (java.sql.SQLException ex) {
						}
						Status currStatus = null;
						try {
							String aName = rs.getString("status");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currStatus = Status.valueOf(aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						TermFormat currFormat = null;
						try {
							String aName = rs.getString("format");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currFormat = TermFormat.valueOf(aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						String currSource = null;
						try {
							currSource = rs.getString("source");
						} catch (SQLException es) {
						}
						// get current row values
						try {
							String aName = Util.cleanXmlString(rs.getString("label"));
							if (!aName.isEmpty()) {
								entry.putPrefLabel(currLang, currFormat, aName, currSource, currStatus);
							}
						} catch (java.sql.SQLException ex) {
						}
						// get current row values
						try {
							String aName = rs.getString(SchemeFactory.iconRoot);
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								entry.putIcon(currLang, aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						try {
							String aName = rs.getString("url");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								entry.putUrl(currLang, aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						// get current row values
						try {
							String aName = Util.cleanXmlString(rs.getString("note"));
							if (!aName.isEmpty()) {
								entry.putScopeNote(currLang, currFormat, aName, currStatus);
							}
						} catch (java.sql.SQLException ex) { // a column may
							// be missing
							// for a given
							// language
						}
						for (Term nameLang : aScheme.getTitle()) {
							Status currLStatus = currStatus;
							try {
								String aStatus = rs.getString("status-" + nameLang.getLang());
								if (aStatus != null && aStatus.length() > 0) {
									// log.debug(nameLang+"="+aName);
									currLStatus = Status.valueOf(aStatus);
								}
							} catch (SQLException es) {
							}
							TermFormat currLFormat = currFormat;
							try {
								String aFormat = rs.getString("format-" + nameLang.getLang());
								if (aFormat != null && aFormat.length() > 0) {
									// log.debug(nameLang+"="+aName);
									currLFormat = TermFormat.valueOf(aFormat);
								}
							} catch (SQLException es) {
							}
							String currLSource = currSource;
							try {
								currLSource = rs.getString("source-" + nameLang.getLang());
							} catch (SQLException es) {
							}
							try {
								String aName = Util.cleanXmlString(rs.getString("label-" + nameLang.getLang()));
								if (!aName.isEmpty()) {
									// log.debug(nameLang+"="+aName);
									entry.putPrefLabel(nameLang.getLang(), currLFormat, aName, currLSource, currLStatus);
								}
							} catch (SQLException es) {
							}
							try {
								String aNote = Util.cleanXmlString(rs.getString("note-" + nameLang.getLang()));
								if (!aNote.isEmpty()) {
									// log.debug(nameLang+"="+aName);
									entry.putScopeNote(nameLang.getLang(), currLFormat, aNote, currLStatus);
								}
							} catch (SQLException es) {
							}
							try {
								String aName = rs.getString(SchemeFactory.iconRoot+"-" + nameLang.getLang());
								if (aName != null && aName.length() > 0) {
									// log.debug(nameLang+"="+aName);
									entry.putIcon(nameLang.getLang(), aName);
								}
							} catch (SQLException es) {
							}
							try {
								String aName = rs.getString("url-" + nameLang.getLang());
								if (aName != null && aName.length() > 0) {
									// log.debug(nameLang+"="+aName);
									entry.putUrl(nameLang.getLang(), aName);
								}
							} catch (java.sql.SQLException ex) {
							}
						}
					}
					// close statement
					stmt.close();
				} catch (SQLException ex) {
					String message = aScheme.getAbout() + ", SQL=" + namesSQL + " : " + ex.toString()+"="+ex.getSQLState();
					log.error(message);
					aScheme.getErrors().add(message);
					Util.printStack(ex);
				}
			}
			if (aliassesSQL != null) {
				try {
					log.debug(aliassesSQL);
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(aliassesSQL);

					while (rs.next()) {
						String currCode = getAbout(aScheme,rs,"about", true);
						if (currCode == null) continue;

						String currOrigin = getAbout(aScheme,rs,"alias", false);
						if (currOrigin == null || currOrigin.isEmpty()) continue;

						Concept entry = aScheme.getConcept(currCode);
						if (entry == null) {
							String message = aScheme.getAbout() + SchemeInterface.separator + currCode
							+ " does not exist. Referred from "
							+ currOrigin;
							log.error(message);
							aScheme.getErrors().add(message);
						}
						aScheme.putAlias(currOrigin, currCode);
					}
					// close statement
					stmt.close();
				} catch (Exception ex) {
					String message = aScheme.getAbout() + ", SQL=" + aliassesSQL + " : " + ex.toString();
					log.error(message);
					aScheme.getErrors().add(message);
					Util.printStack(ex);
				}
			}
			if (synonymsSQL != null) {
				try {
					log.debug(synonymsSQL);
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(synonymsSQL);

					while (rs.next()) {
						String currCode = getAbout(aScheme, rs, "about", true);
						if (currCode == null) continue;
						String currLanguage = TermList.anyLanguage;
						try {
							currLanguage = rs.getString("lang");
							if (currLanguage == null)
								currLanguage = TermList.anyLanguage;
						} catch (SQLException es) {
						}
						Status currStatus = null;
						try {
							String aStatus = rs.getString("status");
							if (aStatus != null && aStatus.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currStatus = Status.valueOf(aStatus);
							}
						} catch (SQLException es) {
						}
						TermFormat currFormat = null;
						try {
							String aName = rs.getString("format");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currFormat = TermFormat.valueOf(aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						String currSource = null;
						try {
							currSource = rs.getString("source");
						} catch (SQLException es) {
						}
						String currSynonym = rs.getString("alternate");
						if (currSynonym == null) {
							String message = aScheme.getAbout()
							+ ": alternate field is not returned by SQL query for alternate labels="
							+ synonymsSQL;
							log.error(message);
							aScheme.getErrors().add(message);
							break;
						} else {
							currSynonym = Util.cleanXmlString(currSynonym);
						}
						Concept entry = aScheme.getConcept(currCode);
						if (entry == null) {
							String message = aScheme.getAbout() + SchemeInterface.separator + currCode
							+ " does not exist. Refered for alternate "
							+ currSynonym + "(" + currLanguage + ")";
							log.error(message);
							aScheme.getErrors().add(message);
						} else {
							if (currSynonym.length() > 0) {
								entry.putAltLabel(currLanguage, currFormat, currSynonym, currSource, currStatus);
							}
							String currOrigin = getAbout(aScheme, rs, "alias", false); // Converting a term based thesaurus to a concept centric one
							if (currOrigin != null && !currOrigin.isEmpty()) {
								aScheme.putAlias(currOrigin, currCode);
							}
						}
					}
					// close statement
					stmt.close();
				} catch (Exception ex) {
					String message = aScheme.getAbout() + ", SQL=" + synonymsSQL + " : " + ex.toString();
					log.error(message);
					aScheme.getErrors().add(message);
					Util.printStack(ex);
				}
			}
			if (notesSQL != null) {
				try {
					log.debug(notesSQL);
					// create and execute a SELECT
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(notesSQL);

					while (rs.next()) {
						String currLang = TermList.anyLanguage;
						String currCode = getAbout(aScheme, rs, "about", true);
						if (currCode == null) continue;
						Concept entry = aScheme.findAbout_Concept(currCode);
						if (entry== null) {
							entry = Concept.ensure(aScheme,currCode);
							entry = aScheme.putConcept(currCode, entry);
						}
						Status currStatus = null;
						try {
							String aName = rs.getString("status");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currStatus = Status.valueOf(aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						TermFormat currFormat = null;
						try {
							String aName = rs.getString("format");
							if (aName != null && aName.length() > 0) {
								// log.debug(currLang+"=="+aName);
								currFormat = TermFormat.valueOf(aName);
							}
						} catch (java.sql.SQLException ex) {
						}
						// get current row values
						try {
							String aLang = rs.getString("lang");
							if (aLang != null && aLang.length() > 0) {
								currLang = aLang;
							}
						} catch (java.sql.SQLException ex) {
						}
						// get current row values
						String relQualifier = null;
						try {
							relQualifier = rs.getString("qual");
							if (relQualifier != null) {
								relQualifier = relQualifier.trim().toLowerCase();
							}
						} catch (java.sql.SQLException ex) {
						}

						try {
							String aName = Util.cleanXmlString(rs.getString("note"));
							if (!aName.isEmpty()) {
								// log.debug(currLang+"=="+aName);
								if (relQualifier == null || relQualifier.isEmpty() || "scope".equals(relQualifier)) {
									entry.putScopeNote(currLang, currFormat, aName, currStatus);
								} else {
									if ("change".equals(relQualifier)) {
										entry.putChangeNote(currLang, currFormat, aName, currStatus);
									} else if ("history".equals(relQualifier)) {
										entry.putHistoryNote(currLang, currFormat, aName, currStatus);
									} else if ("example".equals(relQualifier)) {
										entry.putExample(currLang, currFormat, aName, currStatus);
									} else if ("definition".equals(relQualifier)) {
										entry.putDefinition(currLang, currFormat, aName, currStatus);
									} else if ("editorial".equals(relQualifier)) {
										entry.putEditorialNote(currLang, currFormat, aName, currStatus);
									}
								}
							}
						} catch (java.sql.SQLException ex) {
						}
						for (Term nameLang : aScheme.getTitle()) {
							Status currLStatus = currStatus;
							try {
								String aStatus = rs.getString("status-" + nameLang.getLang());
								if (aStatus != null && aStatus.length() > 0) {
									// log.debug(nameLang+"="+aName);
									currLStatus = Status.valueOf(aStatus);
								}
							} catch (java.sql.SQLException ex) {
							}
							TermFormat currLFormat = currFormat;
							try {
								String aName = rs.getString("format-" + nameLang.getLang());
								if (aName != null && aName.length() > 0) {
									// log.debug(currLang+"=="+aName);
									currFormat = TermFormat.valueOf(aName);
								}
							} catch (java.sql.SQLException ex) {
							}
							try {
								String aNote = Util.cleanXmlString(rs.getString("note-" + nameLang.getLang()));
								if (!aNote.isEmpty()) {
									// log.debug(nameLang+"="+aName);
									entry.putScopeNote(nameLang.getLang(), currLFormat, aNote, currLStatus);
								}
							} catch (java.sql.SQLException ex) {
							}
						}
					}
					// close statement
					stmt.close();
				} catch (SQLException ex) {
					String message = aScheme.getAbout() + ", SQL=" + notesSQL + " : " + ex.toString()+"="+ex.getSQLState();
					log.error(message);
					aScheme.getErrors().add(message);
					Util.printStack(ex);
				}
			}
			try {
				querySQLmatch(con,aScheme,parentsSQL,true,"broader",Concept.class.getMethod("putBroader",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("broader: unknown relation");
			}
			try {
				querySQLmatch(con,aScheme,relatedSQL,true,"related",Concept.class.getMethod("putRelated",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("related: unknown relation");
			}
			try {
				querySQLmatch(con,aScheme,broadMatchSQL,false,"broadmatch",Concept.class.getMethod("putBroadMatch",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("broadmatch: unknown relation");
			}
			try {
				querySQLmatch(con,aScheme,relatedMatchSQL,false,"relatedmatch",Concept.class.getMethod("putRelatedMatch",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("relatedmatch: unknown relation");
			}
			try {
				querySQLmatch(con,aScheme,exactMatchSQL,false,"exactmatch",Concept.class.getMethod("putExactMatch",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("exactmatch: unknown relation");
			}
			try {
				querySQLmatch(con,aScheme,closeMatchSQL,false,"closematch",Concept.class.getMethod("putCloseMatch",String.class,Status.class,String.class));
			} catch (NoSuchMethodException NSM) {
				log.error("closematch: unknown relation");
			}
			if (collectionsSQL != null) {
				try {
					log.debug(collectionsSQL);
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(collectionsSQL);

					while (rs.next()) {
						String currCode = getAbout(aScheme,rs,"about", true);
						if (currCode == null) continue;

						String currCollection = getAbout(aScheme,rs,"collection", false);
						if (currCollection == null || currCollection.isEmpty()) continue;

						Concept entry = aScheme.getConcept(currCode);
						if (entry == null) {
							String message = aScheme.getAbout() + SchemeInterface.separator + currCode
							+ " does not exist. Referred for collection "
							+ currCollection;
							log.error(message);
							aScheme.getErrors().add(message);
						}
						entry.putCollection(currCollection);
					}
					// close statement
					stmt.close();
				} catch (Exception ex) {
					String message = aScheme.getAbout() + ", SQL=" + collectionsSQL + " : " + ex.toString();
					log.error(message);
					aScheme.getErrors().add(message);
					Util.printStack(ex);
				}
			}
			String aProp = null;
			if (notationsDefinitions.size() > 0) {
				TermList languages = aScheme.getTitle();
				for (Map.Entry<String, String> aDefinition : notationsDefinitions.entrySet()) {
					String indexCode = aDefinition.getKey();
					NotationScheme subList = (NotationScheme) aScheme.getManagerInstance().getScheme(indexCode);
					for (Term language : languages) {
						aProp = properties.getProperty(SchemeFactory.titleRoot + "." + indexCode
								+ "-" + language);
						if (aProp != null && aProp.length() > 0)
							subList.putTitle(aProp, null, language.getLang(), null);
						aProp = properties.getProperty(SchemeFactory.descriptionRoot + "." + indexCode
								+ "-" + language);
						if (aProp != null && aProp.length() > 0)
							subList.putDescription(aProp, null, language.getLang(), null);
						aProp = properties.getProperty(SchemeFactory.helpRoot + "." + indexCode
								+ "-" + language);
						if (aProp != null && aProp.length() > 0)
							subList.putHelp(Url.getConfigURL(properties, aProp), language.getLang());
					}
					String indexSQL = poolContext.substitute(aScheme,aDefinition.getValue());
					log.debug(indexSQL);
					try {
						Statement stmt = con.createStatement();
						ResultSet rs = stmt.executeQuery(indexSQL);

						while (rs.next()) {
							String currCode = getAbout(aScheme,rs,"about", true);
							if (currCode == null) continue;

							String currIndex = rs.getString("notation");
							if (currIndex == null) {
								log
								.error(aScheme.getAbout()
										+ ": notation field is not returned by SQL query for notations="
										+ indexSQL);
								break;
							}

							Concept entry = aScheme.getConcept(currCode);
							if (entry == null) {
								String message = aScheme.getAbout() + SchemeInterface.separator + currCode
								+ " does not exist. Referred for notation "
								+ currIndex;
								log.error(message);
								aScheme.getErrors().add(message);
							} else if (currIndex.length() > 0)
								entry.putNotation(indexCode,currIndex);
						}
						// close statement
						stmt.close();
					} catch (Exception ex) {
						String message = aScheme.getAbout() + ", SQL=" + indexSQL + " : " + ex.toString();
						log.error(message);
						aScheme.getErrors().add(message);
						Util.printStack(ex);
					}
				}
			}
			con = SqlPool.freeConnection(con);
			/****
			String aProp = null;
			if (referencesDefinitions.size() > 0) {
				for (String applicationCode : referencesDefinitions.keySet()) {
					String indexSQL = referencesDefinitions.get(applicationCode);
					log.debug(indexSQL);
					try {
						Statement stmt = con.createStatement();
						ResultSet rs = stmt.executeQuery(indexSQL);

						while (rs.next()) {
							String currCode = rs.getString("code");
							if (currCode == null || currCode.length() == 0) {
								log
										.error(schemeCode
												+ ": code field is not returned by SQL query for index="
												+ indexSQL);
								break;
							}
							if (currCode.startsWith(prefixToRemove))
								currCode = currCode.substring(prefixToRemove.length());
							String currApplication = rs.getString("application");
							if (currApplication == null) {
								log
										.error(schemeCode
												+ ": application field is not returned by SQL query for parents="
												+ indexSQL);
								break;
							}
							String currIndex = rs.getString("reference");
							if (currIndex == null) {
								log
										.error(schemeCode
												+ ": index field is not returned by SQL query for parents="
												+ indexSQL);
								break;
							}
							int currCount = rs.getInt("code");
							if (currCount == 0) {
								log
										.error(schemeCode
												+ ": count field is not returned by SQL query for index="
												+ indexSQL);
								break;
							}
							Concept entry = aScheme.getEntry(currCode);
							if (entry == null) {
								log.error(schemeCode + SchemeInterface.separator + currCode
										+ " does not exist. Referred for application+index "
										+ currApplication + "+" + currIndex);
							} else if (currIndex.length() > 0)
								//TODO: Enregistre le d�compte
						}
						// close statement
						stmt.close();
					} catch (Exception ex) {
						log.error(schemeCode + ", SQL=" + indexSQL + " : ", ex);
					}
				}
			}

			con = SqlPool.freeConnection(con);
			 ***/
			
			if (dbVersion != null) { //Serialize the result
				String cacheFileName = configFile.replaceFirst("\\.[a-zA-Z0-9]*$", "")+"-cache-"+SkosUtil.normalizeAbout(dbVersion)+".xml";
				XmlSource.marshall(aScheme, false, cacheFileName);
			}
		}
	}

	protected static String getAbout(ConceptScheme scheme, ResultSet rs, String aboutField, boolean mandatory) {
		String currCode = null;
		try {
			currCode = rs.getString(aboutField);
			if (currCode == null || currCode.length() == 0) {
				if (mandatory) {
					String message = scheme.getAbout()
					+ ": " + aboutField + " is empty for scheme"
					+ scheme.getAbout();
					SchemeFactory.log.error(message);
					scheme.getErrors().add(message);
				}
			} else {
				currCode = scheme.getCleanAbout(currCode);
			}
		} catch (SQLException e) {
			if (mandatory) {
				String message = aboutField+" field missing in SQL result when loading scheme "+scheme.getAbout()+": "+e.toString()+"="+e.getSQLState();
				SchemeFactory.log.error(message);
				scheme.getErrors().add(message);
			}
		}
		return currCode;
	}

}
